package com.tuanzi.loan.business.repository.credithousekeeper.impl;

import com.tuanzi.loan.business.repository.credithousekeeper.CreditCompensatoryWithdrawStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.credithousekeeper.CreditCompensatoryWithdrawStatRequest;
import com.tuanzi.loan.vo.response.credithousekeeper.CreditCompensatoryWithdrawStatResponse;
import org.joda.time.DateTime;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanCardCompensationOrder.LOAN_CARD_COMPENSATION_ORDER;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatCompensationAndWithdrawCollections.STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS;

/**
* @param:
* @Description:    信用卡代偿订单查询sql
* @Author:         xionghao
* @CreateDate:      2018-07-18
*/
@SuppressWarnings({ "rawtypes" })
@Repository
public class CreditCompensatoryWithdrawStatRepositoryImpl extends JOOQBaseRepositoryPlus implements CreditCompensatoryWithdrawStatRepository {
    @Override
    protected TableImpl getTable() {
        return STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS;
    }

    private static String NAME_AS_TIME = "TIME";
    private static String PAY_SUCCESS_UV = "pay_success_uv";
    private static String WITHDRAW_SUCCESS_UV = "withdraw_success_uv";
    private static String INCOME = "income";

    private static int TYPE_SEARCH_ORDER_PAY = 1;
    private static int TYPE_SEARCH_ORDER_WITHDRAW = 2;

    private static Map<Integer,String> ORDER_TYPE = new HashMap<>();
    static {
        ORDER_TYPE.put(1,"取现");
        ORDER_TYPE.put(0,"代偿");
    }

    /**
     * 根据返回的记录生成实体
     */
    private CreditCompensatoryWithdrawStatResponse createResponseByRecord(Record record) {

        CreditCompensatoryWithdrawStatResponse response = new CreditCompensatoryWithdrawStatResponse();
        response.setTime(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME,String.class));
        response.setChannel(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL,Integer.class));
        response.setChannel_from(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL_FROM));
        response.setPrdocu_id(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.APP_PRD_ID));
        response.setOrderType(ORDER_TYPE.get(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ORDER_TYPE)));
        response.setHome_uv(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.HOME_UV,Integer.class));
        response.setOne_page_uv(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ONE_PAGE_UV,Integer.class));
        response.setTwo_page_uv(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TWO_PAGE_UV,Integer.class));
        response.setConfirm_uv(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CONFIRM_UV,Integer.class));
        response.setCommit_pv(record.get(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.COMMIT_PV,Integer.class));
        response.setPaySuccessUV(record.get(PAY_SUCCESS_UV,Integer.class));
        response.setWithdrawSuccessUV(record.get(WITHDRAW_SUCCESS_UV,Integer.class));
        response.setIncome(record.get(INCOME,Double.class));
        return response;
    }


    /**
     * 根据请求条件，构造查询
     */
    private SelectQuery createQuery(CreditCompensatoryWithdrawStatRequest searchRequest){

        Table statTable = generateStat(searchRequest).asTable("stat");
        Table loanTable = generateLoan(searchRequest).asTable("loan");

        return dslContext.select(
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.APP_PRD_ID),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL_FROM),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ORDER_TYPE),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.HOME_UV),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ONE_PAGE_UV),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TWO_PAGE_UV),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.COMMIT_PV),
                statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CONFIRM_UV),
                loanTable.field(PAY_SUCCESS_UV),
                loanTable.field(WITHDRAW_SUCCESS_UV),
                loanTable.field(INCOME)
                ).from(statTable)
                .leftJoin(loanTable)
                .on(statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME).eq(loanTable.field(NAME_AS_TIME)))
                .and(statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.APP_PRD_ID).eq(loanTable.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID)))
                .and(statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL).eq(loanTable.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL)))
                .and(statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL_FROM).eq(loanTable.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM)))
                .and(statTable.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ORDER_TYPE).eq(loanTable.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE)))
                .getQuery();
    }

    private SelectQuery<Record> generateLoan(CreditCompensatoryWithdrawStatRequest searchRequest) {

        Table order1Table = generateOrder(searchRequest,TYPE_SEARCH_ORDER_PAY).asTable("a");
        Table order2Table = generateOrder(searchRequest,TYPE_SEARCH_ORDER_WITHDRAW).asTable("b");

        return dslContext.select(
                order1Table.field(NAME_AS_TIME),
                order1Table.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID),
                order1Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL),
                order1Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM),
                order1Table.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE),
                order1Table.field(PAY_SUCCESS_UV),
                order2Table.field(WITHDRAW_SUCCESS_UV),
                order2Table.field(INCOME)
        ).from(order1Table)
                .leftJoin(order2Table).on(order1Table.field(NAME_AS_TIME).eq(order2Table.field(NAME_AS_TIME)))
                .and(order1Table.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID).eq(order2Table.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID)))
                .and(order1Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL).eq(order2Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL)))
                .and(order1Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM).eq(order2Table.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM)))
                .and(order1Table.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE).eq(order2Table.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE)))
                .getQuery();
    }

    private SelectQuery<Record> generateOrder(CreditCompensatoryWithdrawStatRequest searchRequest,int type) {

        Condition condition = DSL.trueCondition();

        List<SelectField<?>> selectFields = new ArrayList<>();
        selectFields.add(DSL.field(
                DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR,
                        LOAN_CARD_COMPENSATION_ORDER.CTIME, DSL.inline("%Y-%m-%d"))).as(NAME_AS_TIME));
        selectFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID));
        selectFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL));
        selectFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM));
        selectFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE));

        if (type == TYPE_SEARCH_ORDER_PAY){
            condition = condition.and(DSL.field(LOAN_CARD_COMPENSATION_ORDER.STATUS).in(12,13,15,16));
            selectFields.add(DSL.countDistinct(LOAN_CARD_COMPENSATION_ORDER.USERID).as(PAY_SUCCESS_UV));
        }else {
            condition = condition.and(DSL.field(LOAN_CARD_COMPENSATION_ORDER.STATUS).eq(12));
            selectFields.add(DSL.countDistinct(LOAN_CARD_COMPENSATION_ORDER.USERID).as(WITHDRAW_SUCCESS_UV));
            selectFields.add(DSL.sum(DSL.field("FORMAT({0}, {1})",SQLDataType.NUMERIC(7,2),LOAN_CARD_COMPENSATION_ORDER.FEE,2)).as(INCOME));
        }

        condition = checkConditions(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID,condition,searchRequest.getProduct_id());
        condition = checkConditions(LOAN_CARD_COMPENSATION_ORDER.CHANNEL,condition,searchRequest.getChannel_id());
        condition = checkConditions(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM,condition,searchRequest.getChannel_from());
        condition = checkConditions(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE,condition,searchRequest.getOrderType());

        //日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (Objects.nonNull(searchRequest.getStartDate())) {
            condition = condition.and(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CTIME).gt(
                    Timestamp.valueOf(sdf.format(searchRequest.getStartDate()) + " 00:00:00")));
        }
        if( Objects.nonNull(searchRequest.getEndDate()) ){
            condition = condition.and(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CTIME).lt(
                    Timestamp.valueOf(sdf.format(searchRequest.getEndDate()) + " 23:59:59")));
        }

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.PRODUCT_ID));
        groupFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL));
        groupFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.CHANNEL_FROM));
        groupFields.add(DSL.field(LOAN_CARD_COMPENSATION_ORDER.ORDER_TYPE));
        groupFields.add(
                DSL.field(
                DSL.field("date_format({0}, {1})", SQLDataType.VARCHAR,
                LOAN_CARD_COMPENSATION_ORDER.CTIME, DSL.inline("%Y-%m-%d"))));

        condition = condition.and(DSL.field(LOAN_CARD_COMPENSATION_ORDER.TYPE).eq(20));

        return dslContext.select(selectFields)
                .from(LOAN_CARD_COMPENSATION_ORDER)
                .where(condition)
                .groupBy(groupFields)
                .orderBy(LOAN_CARD_COMPENSATION_ORDER.CTIME.desc())
                .getQuery();
    }

    private SelectQuery<Record> generateStat(CreditCompensatoryWithdrawStatRequest searchRequest) {

        Condition condition = DSL.trueCondition();
        condition = checkConditions(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.APP_PRD_ID,condition,searchRequest.getProduct_id());
        condition = checkConditions(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL,condition,searchRequest.getChannel_id());
        condition = checkConditions(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.CHANNEL_FROM,condition,searchRequest.getChannel_from());
        condition = checkConditions(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.ORDER_TYPE,condition,searchRequest.getOrderType());

        //时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (Objects.nonNull(searchRequest.getStartDate())) {
            condition = condition.and(DSL.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME).ge(sdf.format(searchRequest.getStartDate())));
        }
        if( Objects.nonNull(searchRequest.getEndDate()) ){
            condition = condition.and(DSL.field(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME).le(sdf.format(searchRequest.getEndDate())));
        }

        return dslContext.select(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.fields())
                .from(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS)
                .where(condition)
                .orderBy(STAT_COMPENSATION_AND_WITHDRAW_COLLECTIONS.TIME.desc())
                .getQuery();
    }

    /**
     * 创建检查条件
     */
    private Condition checkConditions(TableField tableField,Condition condition,Object value){
        if (value != null && !value.equals("")){
            condition = condition.and(tableField.eq(value));
        }
        return condition;
    }

    @Override
    public Page<CreditCompensatoryWithdrawStatResponse> list(CreditCompensatoryWithdrawStatRequest searchRequest, Pageable pageable) {
        //默认展示上一天的数据
        if (searchRequest.getStartDate() == null && searchRequest.getEndDate() == null){
            Date startDate = new DateTime().plusDays(-1).toDate();
            searchRequest.setStartDate(startDate);
            searchRequest.setEndDate(startDate);
        }
        SelectQuery selectQuery = this.createQuery(searchRequest);
        return search(selectQuery,pageable,record -> createResponseByRecord(record));
    }
}
